import os
from openpyxl import load_workbook
from collections import defaultdict

def check_duplicate_sheets(folder_path):
    # Dictionary to store sheet names and their corresponding files
    sheet_occurrences = defaultdict(list)
    
    # Iterate through all files in the folder
    for filename in os.listdir(folder_path):
        # Check if file is an Excel file
        if filename.endswith(('.xlsx', '.xls', '.xlsm')):
            file_path = os.path.join(folder_path, filename)
            try:
                # Load the workbook
                wb = load_workbook(file_path, read_only=True)
                
                # Get sheet names and add them to dictionary
                for sheet_name in wb.sheetnames:
                    sheet_occurrences[sheet_name].append(filename)
                
                wb.close()
            except Exception as e:
                print(f"Error reading {filename}: {str(e)}")
    
    # Find and print duplicate sheet names
    duplicates_found = False
    print("\nDuplicate sheet names found:")
    print("-" * 50)
    
    for sheet_name, files in sheet_occurrences.items():
        if len(files) > 1:
            duplicates_found = True
            print(f"\nSheet name: '{sheet_name}' appears in files:")
            for file in files:
                print(f"  - {file}")
    
    if not duplicates_found:
        print("No duplicate sheet names found across files.")

# Usage example
folder_path = "./婧秋师姐_数据/2/乙酸乙酯提取"  # Replace with your folder path
check_duplicate_sheets(folder_path)